﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using fmtx.Framework.DBHelper;

namespace SqlPressureTest
{
    class Program
    {
        static void Main(string[] args)
        {
            for (int i = 0; i < 5000; i++)
            {
                SelectData();
            }
            var inputkey = Console.ReadLine();
            while (inputkey != "exit")
            {
                inputkey = Console.ReadLine();
            }
        }

        private static void SelectData()
        {
            string sql = @"select case ur.RoleType when 2 then '开发' when 3 then '测试' else '无' end 岗位,
                su.TrueName 姓名,soz.OrganizationName 所属部门,isnull(rwc.a,0) 任务总数, sum(wc)按时完成任务数
                ,cast(cast(sum(wc)as decimal(10,2))/cast(rwc.a as decimal(10,2))as decimal(10,2)) 任务按时完成率
                ,cast(cast(sum(wc)*30 as decimal(10,2))/rwc.a as decimal(10,2)) 任务按时完成率得分
                ,SUM(llt) 延迟一天以上任务数
                ,case when (20-sum(llt)*2>=0)then(20-sum(llt)*2)else 0 end 登录及时率得分
                ,sum(tq)提前时长,sum(yh)延后时长
                ,isnull(dfr.qcount,0) 总缺陷数
                ,case when isnull(code.CodeRowNumber,0)>0 and ur.RoleType=2 then cast(cast(cast(isnull(code.CodeRowNumber,0) as decimal(10,2))/1000 as decimal(10,2))as varchar(5)) else case when ur.RoleType=3 then '0' else '(异常)' end end 代码行数
                ,isnull(case when code.CodeRowNumber=0 then 0 else cast(cast(dfr.qcount*1000 as decimal(10,2))/code.CodeRowNumber as decimal(10,3)) end,0) 千行代码bug率
                 from 
                (select UserID,case when m>=0 then 1 else 0 end as wc
                ,case when (lt*-1)>24*60 then 1 else 0 end as llt
                ,case when sjl-yjl<0  then cast(cast(abs(sjl-yjl) as decimal(10,2)) as decimal(10,2))else 0 end tq
                ,case when sjl-yjl>0  then cast(cast(abs(sjl-yjl) as decimal(10,2)) as decimal(10,2))else 0 end yh
                 from (
               SELECT  at.UserID, isnull(datediff(MINUTE,max(ht.CompletedTime),at.EstimateEndTime),0)as m--如果是正数则提前完成
				,isnull(datediff(MINUTE,min(ht.StartDealTime),at.EstimateEndTime),0)lt
				,at.EstimateWorkLoad yjl,sum(ht.ActualWorkload) sjl
				FROM    dbo.AssignTask at left JOIN
				  dbo.HandleTask ht ON at.AssignTaskID = ht.AssignTaskID
				  inner join HandleProcess hp on hp .HandleProcessID=at.HandleProcessID 
				   where at.IsIssued=1 and at.AddTime>='2012-02-01 00:00:01' and ht.CompletedTime<='2014-03-02 23:59:59'
                 group by at.AssignTaskID,at.UserID,at.EstimateEndTime,at.EstimateWorkLoad)a
                union all
                select UserID,case when m>=0 then 1 else 0 end as wc
                ,case when (lt*-1)>24*60 then 1 else 0 end as llt
                ,case when sjl-yjl<0  then cast(cast(abs(sjl-yjl) as decimal(10,2)) as decimal(10,2))else 0 end tq
                ,case when sjl-yjl>0  then cast(cast(abs(sjl-yjl) as decimal(10,2)) as decimal(10,2))else 0 end yh
                 from ( 
                SELECT dt.UserID, isnull(datediff(MINUTE,dc.ActualCompletedTime,dt.ExpectedCompletionDate),0)as m
                ,isnull(datediff(MINUTE,dc.StartDealTime,dt.ExpectedCompletionDate),0)lt
                ,dt.ProjectedWorkload yjl,dc.ActualWorkload sjl
                FROM    dbo.DevTask dt inner JOIN
                     dbo.DevCompleted dc ON dt.DevTaskID = dc.DevTaskID where dt.IsIssued=1
                     and dt.DecomposeTime>='2012-02-01 00:00:01' and dc.ActualCompletedTime<='2014-03-02 23:59:59' )b
                 union all
                select UserID,case when m>=0 then 1 else 0 end as wc
                ,case when (lt*-1)>24*60 then 1 else 0 end as llt
                ,case when sjl-yjl<0  then cast(cast(abs(sjl-yjl) as decimal(10,2)) as decimal(10,2))else 0 end tq
                ,case when sjl-yjl>0  then cast(cast(abs(sjl-yjl) as decimal(10,2)) as decimal(10,2))else 0 end yh
                 from (     
                SELECT  tt.UserID
                ,isnull(datediff(MINUTE,max(tc.ActualCompletedTime),tt.ExpectedCompletionDate),0)as m
                ,isnull(datediff(MINUTE,min(tc.StartDealTime),tt.ExpectedCompletionDate),0)lt
                ,tt.ProjectedWorkload yjl,sum(tc.ActualWorkload) sjl
                FROM   dbo.TestTask tt inner JOIN
                  dbo.TestTaskInRelease ttr ON tt.TestTaskID = ttr.TestTaskID INNER JOIN
                 dbo.TestCompleted tc ON ttr.TestTaskInReleaseID = tc.TestTaskInReleaseID 
                 where tt.IsIssued=1
                 and tt.DecomposeTime>='2012-02-01 00:00:01' and tc.ActualCompletedTime<='2014-03-02 23:59:59'
                 group by tt.TestTaskID,tt.UserID,tt.ExpectedCompletionDate,tt.ProjectedWorkload
                 )c)temp
                 left join SysUser su on su.UserID = temp.UserID 
                 left join (select distinct sr.RoleType,su.UserID from UserRole ur,SysRole sr,SysUser su where su.UserID = ur.UserID 
                 and ur.RoleID = sr.RoleID  and sr.RoleType != 1) ur on ur.UserID = temp.UserID
                 left join SysOrganization soz on su.OrganizationID=soz.OrganizationID
                 left join 
                 (SELECT  DevTask.UserID, isnull(COUNT(DefectRecords.DefectID),0)qcount
                FROM   dbo.DefectRecords INNER JOIN
                   dbo.TestTask ON dbo.DefectRecords.TestTaskID = dbo.TestTask.TestTaskID INNER JOIN
                   dbo.DevTask ON dbo.TestTask.DevTaskID = dbo.DevTask.DevTaskID INNER JOIN
                   dbo.DevCompleted ON dbo.DevTask.DevTaskID = dbo.DevCompleted.DevTaskID
                   where 1=1 and DevCompleted.ActualCompletedTime between '2012-02-01 00:00:01' and '2014-03-02 23:59:59'
                    group by DevTask.UserID)dfr
                     on dfr.UserID=su.UserID
                     left join
                     (
                       SELECT  dt.UserID, sum(dc.CodeRowNumber)CodeRowNumber
	                FROM  dbo.DevTask dt INNER JOIN
                      dbo.DevCompleted dc ON dt.DevTaskID = dc.DevTaskID
                    where 1=1 and dc.ActualCompletedTime between '2012-02-01 00:00:01' and '2014-03-02 23:59:59'
                     group by dt.UserID
                     )code on code.UserID=su.UserID
                    left join (
                    select UserID,SUM(a)a from 
                    (select userid ,COUNT(userid)a from DevTask dt
                    where dt.IsIssued=1 and dt.DecomposeTime between '2012-02-01 00:00:01' and '2014-03-02 23:59:59'
                    group by userid
                    union all
                    select userid,COUNT(userid)a from TestTask tt
                    right join TestTaskInRelease ttr on ttr.TestTaskID=tt.TestTaskID
                    inner join TestCompleted tc on tc.TestTaskInReleaseID=ttr.TestTaskInReleaseID
                    where tt.IsIssued=1 and tt.DecomposeTime between '2012-02-01 00:00:01' and '2014-03-02 23:59:59'
                    group by UserID
                    union all
                    select userid,COUNT(userid)a from AssignTask at
                    where at.IsIssued=1 and at.AddTime between '2012-02-01 00:00:01' and '2014-03-02 23:59:59'
                    group by UserID)temp
                    group by UserID
                    )rwc on rwc.UserID=su.UserID 
                    where 1=1 
                 group by su.TrueName,temp.userId,ur.RoleType,soz.OrganizationName,code.CodeRowNumber,dfr.qcount,rwc.a
                  order by 千行代码bug率 ";
            var timebg = DateTime.Now.Ticks;
            var data = SqlHelper.ExecuteDataTable(System.Data.CommandType.Text, "select * from dbo.StateChangeRecord");
           var timeen = DateTime.Now.Ticks;
           var c = TimeSpan.FromTicks(timeen - timebg);
           Console.WriteLine("查询完成！耗时：" + c.TotalMilliseconds);

        }
    }
}
